const { Pool } = require("pg");
const dbConfig = require("../../dbconfig");

/**
 * 查询单词信息
 * @param {string} word - 要查询的单词
 * @param {boolean} onlyIELTS - 是否只查询IELTS单词
 * @returns {Promise<object|null>} 单词信息或null
 */
async function queryWord(word, onlyIELTS = false) {
  // 创建数据库连接池
  const pool = new Pool(dbConfig);

  try {
    const client = await pool.connect();
    try {
      let queryText = "SELECT * FROM ENWORDS WHERE word = $1";
      let queryParams = [word];

      if (onlyIELTS) {
        queryText += " AND ielst = $2";
        queryParams.push("YES");
      }

      queryText += " LIMIT 1";

      const result = await client.query(queryText, queryParams);
      return result.rows.length > 0 ? result.rows[0] : null;
    } finally {
      client.release();
    }
  } catch (error) {
    console.error("！！！数据库查询出错:", error);
    return null;
  } finally {
    await pool.end();
  }
}

/**
 * 批量查询单词信息，优先返回IELTS单词
 * @param {Array<string>} words - 要查询的单词数组
 * @param {boolean} onlyIELTS - 是否只查询IELTS单词
 * @param {number} limit - 限制返回结果数量
 * @returns {Promise<Array>} 单词信息数组
 */
async function queryWords(words, limit = 10) {
  // 创建数据库连接池
  const pool = new Pool(dbConfig);

  try {
    const client = await pool.connect();
    try {
      // 如果没有单词要查询，直接返回空数组
      if (!words || words.length === 0) {
        return [];
      }
      // 构建查询语句，优先选择IELTS单词
      let queryText = `SELECT * FROM ENWORDS WHERE word = any (ARRAY[${words}])`;

      // 通过ORDER BY CASE语句优先排序IELTS单词
      queryText += " ORDER BY CASE WHEN ielst = 'YES' THEN 0 ELSE 1 END LIMIT " + limit;
      const result = await client.query(queryText);
      return result.rows;
    } finally {
      client.release();
    }
  } catch (error) {
    console.error("！！！数据库查询出错:", error);
    return [];
  } finally {
    await pool.end();
  }
}

module.exports = {
  queryWord,
  queryWords,
};
